from sqlalchemy.sql.schema import Column, ForeignKey
from sqlalchemy.sql.sqltypes import Integer, DateTime, Boolean
from sqlalchemy.sql.functions import func
from sqlalchemy.orm import relationship

from app.core.database import Base


class UserRole(Base):
    """用户-角色关联表 - 多对多关系"""
    __tablename__ = "user_roles"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    role_id = Column(Integer, ForeignKey("roles.id", ondelete="CASCADE"), nullable=False)
    
    # 关联状态
    is_active = Column(Boolean, default=True, nullable=False)
    
    # 审计字段
    assigned_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # 关联关系
    user = relationship("User", back_populates="user_roles", overlaps="roles,users")
    role = relationship("Role", back_populates="user_roles", overlaps="roles,users")
    
    def __repr__(self):
        return f"<UserRole(user_id={self.user_id}, role_id={self.role_id}, is_active={self.is_active})>"
    
    def to_dict(self):
        """转换为字典格式"""
        return {
            "id": self.id,
            "user_id": self.user_id,
            "role_id": self.role_id,
            "is_active": self.is_active,
            "assigned_at": self.assigned_at.isoformat() if self.assigned_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
            "role": self.role.to_dict() if hasattr(self.role, 'to_dict') else str(self.role)
        }